package com.huangpf.util.excelUtil;

import com.google.gson.Gson;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 根据xml的配置解析excel文件，xml的配置参考excl_parse.xml文件。
 * Created by huangpf on 2016/9/16.
 */
public class ExcelParse {
    private List<Map<String, Object>> successList = new ArrayList<>(); // 返回解析正确的数据
    private List<Map<String, Object>> errorList = new ArrayList<>(); // 返回解析错误的数据
    private String errorInfo = ""; // 校验错误信息
    private static final String DEFAULT_FORMAT = "yyyy-MM-dd HH:mm:ss";

    public ExcelParse(String filePath, String xmlPath){
        Workbook wb = null;
        Document document = null;
        try {
            wb = WorkbookFactory.create(new File(filePath));// 根据文件自动返回HSSFWorkbook（2007以前的excel）、XSSFWorkbook（2007以后的excel）

            SAXReader reader = new SAXReader();
            document = reader.read(new FileInputStream(xmlPath));
        } catch (IOException | DocumentException | InvalidFormatException e) {
            e.printStackTrace();
        }

        parseExcel(wb, document.getRootElement());
    }

    public ExcelParse(Workbook wb, Element root){
        parseExcel(wb, root);
    }

    /**
     * 根据对象类型获取导入成功过数据
     * @param clz
     * @return
     */
    public List getSuccessList(Class clz){
        Gson gson = new Gson();
        List result = new ArrayList();
        for (Map<String, Object> map : successList){
            result.add(gson.fromJson(gson.toJson(map), clz));
        }

        return result;
    }

    public void parseExcel(Workbook wb, Element root){
        List<Element> sheetElements = root.elements("sheet");

        // 多个sheet解析 暂时只解析单个sheet
        /*for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            parseSheet(wb.getSheetAt(i), sheetElements.get(i));
        }*/

        parseSheet(wb.getSheetAt(0), sheetElements.get(0));
    }


    /**
     * 获取每一页的数据
     * @param sheet
     * @param sheetElement
     * @return
     */
    private void parseSheet(Sheet sheet, Element sheetElement){
        Element header = sheetElement.element("header"); // 获取header
        List<Element> cells = header.elements("cell");
        boolean isShow = "true".equals(header.attributeValue("isshow")); // 导入文件第一行是否为标题，为标题，则取数据从第二行开始取
        int i = isShow ? sheet.getFirstRowNum() +1 : sheet.getFirstRowNum();
        for (; i <= sheet.getLastRowNum(); i++){
            errorInfo = "";
            Map<String, Object> map = parseRow(sheet.getRow(i), cells);
            if ("".equals(errorInfo)){
                successList.add(map);
            } else {
                map.put("errorInfo", errorInfo);
                errorList.add(map);
            }
        }
    }

    /**
     * 获取每一行的数据
     * @param row
     * @param cells
     * @return
     */
    private Map<String, Object> parseRow(Row row, List<Element> cells) {
        Map<String, Object> map = new HashMap<>();

        for (int i = 0; i < cells.size(); i++){
            Element cellElement = cells.get(i);

            String name = cellElement.attributeValue("name");
            String[] names = name.split("\\.");
            if (names.length > 1){
                Map<String, Object> subMap = new HashMap<>();
                subMap.put(names[names.length - 1], formatCellValue(row.getCell(i), cellElement));
                for (int j = 1; j < names.length - 1; j++){
                    Map<String, Object> temMap = subMap;
                    subMap.put(names[names.length - 1 - j], temMap);
                }

                map.put(names[0],subMap);
            } else {
                map.put(name, formatCellValue(row.getCell(i), cellElement));
            }

        }

        return map;
    }

    /**
     * 格式化cell的value
     * @param cell
     * @param cellElement
     * @return
     */
    private Object formatCellValue(Cell cell, Element cellElement){
        String cellValue = getCellValue(cell);

        String type = cellElement.attributeValue("type"); // type(1:string 2:numeric 3:datatime)
        String format = cellElement.attributeValue("format"); // format: column format(numeric format:0.00|#.##..., datatime format:yyyy-MM-dd...)
        String nullAble = cellElement.attributeValue("nullable"); // nullable: option[yes|no]
        String minsize = cellElement.attributeValue("minsize"); // type为1时字符串的最小长度，type为2时的最小值
        String maxsize = cellElement.attributeValue("maxsize"); // type为1时字符串的最大长度，type为2时的最大值

        // 判断是否为空
        if ("no".equals(nullAble) && (cellValue == null || cellValue.length() <= 0)) {
            errorInfo = errorInfo + cellElement.attributeValue("desc") + "不能为空。";
            return cellValue;
        }

        switch (type){
            case "1" :
                if (minsize != null && minsize.length() > 0 && cellValue.length() < Integer.valueOf(minsize)){
                    errorInfo = errorInfo + cellElement.attributeValue("desc") + "数据长度需大于" + minsize + "。";
                    return cellValue;
                }
                if (maxsize != null && maxsize.length() > 0 && cellValue.length() > Integer.valueOf(maxsize)){
                    errorInfo = errorInfo + cellElement.attributeValue("desc") + "数据长度需小于" + maxsize + "。";
                    return cellValue;
                }
                return cellValue;
            case "2" :
                if (!cellValue.matches("^-?\\d+\\.[\\d]+") && !cellValue.matches("^-?\\d+$")){
                    errorInfo = errorInfo + cellElement.attributeValue("desc") + "必须为数字。";
                    return cellValue;
                }

                if (minsize != null && minsize.length() > 0 && Double.valueOf(cellValue) < Double.valueOf(minsize)){
                    errorInfo = errorInfo + cellElement.attributeValue("desc") + "值需大于" + minsize + "。";
                    return cellValue;
                }
                if (maxsize != null && maxsize.length() > 0 && Double.valueOf(cellValue) > Integer.valueOf(maxsize)){
                    errorInfo = errorInfo + cellElement.attributeValue("desc") + "值需小于" + maxsize + "。";
                    return cellValue;
                }

                if (format == null || format.length() <= 0){
                    return cellValue;
                } else {
                    DecimalFormat df = new DecimalFormat(format);
                    return df.format(Double.valueOf(cellValue));
                }
            case "3" :
                try {
                    SimpleDateFormat formatter = null;
                    if (format == null || format.length() <= 0){
                        formatter = new SimpleDateFormat(DEFAULT_FORMAT);
                    } else {
                        formatter = new SimpleDateFormat(format);
                    }
                    return formatter.format(HSSFDateUtil.getJavaDate(Double.valueOf(cellValue)));
                } catch (Exception e) {
                    errorInfo = errorInfo + cellElement.attributeValue("desc") + "日期格式不对。";
                    return cellValue;
                }

            default:
                return cellValue;
        }
    }

    /**
     * 获取cell的值
     * @param cell
     * @return
     */
    private String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case HSSFCell.CELL_TYPE_STRING:
                System.out.println(cell.getStringCellValue());
                return cell.getStringCellValue();
            case HSSFCell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case HSSFCell.CELL_TYPE_BLANK:
                return "";
            case HSSFCell.CELL_TYPE_BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case HSSFCell.CELL_TYPE_ERROR:
                return String.valueOf(cell.getErrorCellValue());
        }
        return "";
    }

    public List<Map<String, Object>> getSuccessList() {
        return successList;
    }

    public void setSuccessList(List<Map<String, Object>> successList) {
        this.successList = successList;
    }

    public List<Map<String, Object>> getErrorList() {
        return errorList;
    }

    public void setErrorList(List<Map<String, Object>> errorList) {
        this.errorList = errorList;
    }

    public String getErrorInfo() {
        return errorInfo;
    }

    public void setErrorInfo(String errorInfo) {
        this.errorInfo = errorInfo;
    }
}
